*Start with merging separte datasets containing single or small clusters of variables into one large dataset. Note, for merge, the file named "Population.dta" is used as master
sort code year
xtset code year
merge code year using Homicides_All CIDE_org_crime_events Population_Males Population_Females Males_Per_100_Females Household_Size Households_Total Number_Head_of_HH_Female Population_15_29 Population_15_29_Females Population_15_29_Males Indigenous_Pop_Numb HDI_Services HDI_Per_Capita Pobreza Police_Municipal Sec_Spending_Share Ingresos_Total_Corrientes Ingresos_Impuestos_Corrientes Ingresos_Quotas_SS_Corrientes Ingresos_Contrib_De_Mejoras_Corr Ingresos_Derechos_Corrientes Ingresos_Productos_Corrientes Ingresos_Aprovechamientos_Corr Ingresos_Particip_Corr Ingresos_Aport_Fed_Est_Corr Ingresos_Otros_Corrientes Ingresos_Financiamientos_Corr Ingresos_Disp_Inicial_Corr Egresos_Total_Corr Egresos_Serv_Person_Corr Egresos_Materiales_Corr Egresos_Serv_Gen_Corr Egresos_Transfers_Corr Egresos_Bienes_Corr Egresos_Inver_Publ_Corr Egresos_Inver_Fin_Corr Egresos_Otros_Corr Egresos_Deuda_Corr Egresos_Disp_Fin_Corr Electoral_Comp
drop _merge1 _merge2 _merge3 _merge4 _merge5 _merge6 _merge7 _merge8 _merge9 _merge10 _merge11 _merge12 _merge13 _merge14 _merge15 _merge16 _merge17 _merge18 _merge19 _merge20 _merge21 _merge22 _merge23 _merge24 _merge25 _merge26 _merge27 _merge28 _merge29 _merge30 _merge31 _merge32 _merge33 _merge34 _merge35 _merge36 _merge37 _merge38 _merge39 _merge40  _merge41 _merge 

*Drop variables that will not be used in future analyses
drop CIDE_deaths_agr CIDE_evt_agr CIDE_deaths_enf  CIDE_evt_enf CIDE_OCG_deaths_enf CIDE_OCG_evt_enf CIDE_nr_executed CIDE_evt_executions CIDE_events CIDE_share_exec_events CIDE_deaths CIDE_OC_events CIDE_share_exec_OC_events Population_15_29 Population_15_29_Females HDI_Per_Capita poverty_moderate poverty_extreme vul_cs_por vul_ing_por np_nv_por pa1cs_por p3mcs_por ego_edu_por cassa_por casso_por ccev_por casbv_por caa_por piilb_por piilbm_por sec_true




 

* Ensure that all observations are labeled with the municipality’s name
by code, sort: replace municipio= municipio[_n-1] if municipio==""
sort code
drop if municipio=="No especificado"
drop if municipio =="Otros estados"
drop if municipio =="Otros municipios"

*Include an identifier for the state a municipality is located in
merge code using Estado_ID
drop _merge
sort year

* Include the Consumer Price Index (baseline 2010)
merge year using CPI_Baseline_2010
drop _merge
sort code

* Add the area size for each municipal government
merge code using Area_size
drop _merge

* The variables related to organized crime violence are modified in a way that missing values between 2007-2010 (for which data are available) are converted to 0s, indicating that no organized crime homicide was reported. Missing values before 2007 and after 2010 are real missing values
sort code year 
sort code year 
replace CIDE_OC_deaths = 0 if CIDE_OC_deaths ==.
replace CIDE_OC_deaths =. if year > 2010
replace CIDE_OC_deaths =. if year < 2007

* Local revenue and spending variables are modified in a way that a missing value represents zero income/spending in a specific income/spending dimension. At the same time, if the total revenue / spending variable indicates a missing value, this means that a local government did not report these data
replace Ingresos_Impuestos_Corrientes =0 if Ingresos_Impuestos_Corrientes==.
replace Ingresos_Quotas_SS_Corrientes =0 if Ingresos_Quotas_SS_Corrientes ==.
replace Ingresos_Contrib_De_Mejoras_Corr =0 if Ingresos_Contrib_De_Mejoras_Corr ==.
replace Ingresos_Derechos_Corrientes =0 if Ingresos_Derechos_Corrientes ==.
replace Ingresos_Productos_Corrientes =0 if Ingresos_Productos_Corrientes ==.
replace Ingresos_Aprovechamientos_Corr =0 if Ingresos_Aprovechamientos_Corr ==.
replace Ingresos_Particip_Corr =0 if Ingresos_Particip_Corr ==.
replace Ingresos_Aport_Fed_Est_Corr =0 if Ingresos_Aport_Fed_Est_Corr ==.
replace Ingresos_Otros_Corrientes =0 if Ingresos_Otros_Corrientes ==.
replace Ingresos_Financiamientos_Corr =0 if Ingresos_Financiamientos_Corr ==.
replace Ingresos_Disp_Inicial_Corr =0 if Ingresos_Disp_Inicial_Corr ==.

replace Egresos_Serv_Person_Corr =0 if Egresos_Serv_Person_Corr ==.
replace Egresos_Materiales_Corr =0 if Egresos_Materiales_Corr ==.
replace Egresos_Serv_Gen_Corr =0 if Egresos_Serv_Gen_Corr ==.
replace Egresos_Transfers_Corr =0 if Egresos_Transfers_Corr ==.
replace Egresos_Bienes_Corr =0 if Egresos_Bienes_Corr ==.
replace Egresos_Inver_Publ_Corr =0 if Egresos_Inver_Publ_Corr ==.
replace Egresos_Inver_Fin_Corr =0 if Egresos_Inver_Fin_Corr ==.
replace Egresos_Otros_Corr =0 if Egresos_Otros_Corr ==.
replace Egresos_Deuda_Corr =0 if Egresos_Deuda_Corr ==.
replace Egresos_Disp_Fin_Corr =0 if Egresos_Disp_Fin_Corr ==.

* All revenue/ spending dimensions are put to missing when the total revenue / spending variable reflects a missing value
replace Ingresos_Impuestos_Corrientes =. if Ingresos_Total_Corrientes ==.
replace Ingresos_Quotas_SS_Corrientes =. if Ingresos_Total_Corrientes ==.
replace Ingresos_Contrib_De_Mejoras_Corr =. if Ingresos_Total_Corrientes ==.
replace Ingresos_Derechos_Corrientes =. if Ingresos_Total_Corrientes ==.
replace Ingresos_Productos_Corrientes =. if Ingresos_Total_Corrientes ==.
replace Ingresos_Aprovechamientos_Corr =. if Ingresos_Total_Corrientes ==.
replace Ingresos_Particip_Corr =. if Ingresos_Total_Corrientes ==.
replace Ingresos_Aport_Fed_Est_Corr =. if Ingresos_Total_Corrientes ==.
replace Ingresos_Otros_Corrientes =. if Ingresos_Total_Corrientes ==.
replace Ingresos_Financiamientos_Corr =. if Ingresos_Total_Corrientes ==.
replace Ingresos_Disp_Inicial_Corr =. if Ingresos_Total_Corrientes ==.

replace Egresos_Serv_Person_Corr =. if Egresos_Total_Corr ==.
replace Egresos_Materiales_Corr =. if Egresos_Total_Corr ==.
replace Egresos_Serv_Gen_Corr =. if Egresos_Total_Corr ==.
replace Egresos_Transfers_Corr =. if Egresos_Total_Corr ==.
replace Egresos_Bienes_Corr =. if Egresos_Total_Corr ==.
replace Egresos_Inver_Publ_Corr =. if Egresos_Total_Corr ==.
replace Egresos_Inver_Fin_Corr =. if Egresos_Total_Corr ==.
replace Egresos_Otros_Corr =. if Egresos_Total_Corr ==.
replace Egresos_Deuda_Corr =. if Egresos_Total_Corr ==.
replace Egresos_Disp_Fin_Corr =. if Egresos_Total_Corr ==.

* The various local revenue/ spending variables are adjusted to the evolution of the Mexican CPI, so that they reflect real values
foreach var of varlist Ingresos_Total_Corrientes- Egresos_Disp_Fin_Corr{
replace `var' = `var'*100/ cpi_baseline_2010
}
rename (Ingresos_Total_Corrientes Ingresos_Impuestos_Corrientes Ingresos_Quotas_SS_Corrientes Ingresos_Contrib_De_Mejoras_Corr Ingresos_Derechos_Corrientes Ingresos_Productos_Corrientes Ingresos_Aprovechamientos_Corr Ingresos_Particip_Corr Ingresos_Aport_Fed_Est_Corr Ingresos_Otros_Corrientes Ingresos_Financiamientos_Corr Ingresos_Disp_Inicial_Corr Egresos_Total_Corr Egresos_Serv_Person_Corr Egresos_Materiales_Corr Egresos_Serv_Gen_Corr Egresos_Transfers_Corr Egresos_Bienes_Corr Egresos_Inver_Publ_Corr Egresos_Inver_Fin_Corr Egresos_Otros_Corr Egresos_Deuda_Corr Egresos_Disp_Fin_Corr) (Ingresos_Total_Real Ingresos_Impuestos_Real Ingresos_Quotas_SS_Real Ingresos_Contrib_De_Mejoras_Real Ingresos_Derechos_Real Ingresos_Productos_Real Ingresos_Aprovechamientos_Real Ingresos_Particip_Real Ingresos_Aport_Fed_Est_Real Ingresos_Otros_Real Ingresos_Financiamientos_Real Ingresos_Disp_Inicial_Real Egresos_Total_Real Egresos_Serv_Person_Real Egresos_Materiales_Real Egresos_Serv_Gen_Real Egresos_Transfers_Real Egresos_Bienes_Real Egresos_Inver_Publ_Real Egresos_Inver_Fin_Real Egresos_Otros_Real Egresos_Deuda_Real Egresos_Disp_Fin_Real)

* Generation of the variable indicating the real local revenue per capita of each municipality
gen Revenue_Cap_Real = Ingresos_Total_Real/ Population

* Generation of a variable reflecting revenues from taxes, user-fees, fees for public construction, penalty fees and social security as share from total revenues
gen Own_Revenue_Share = (( Ingresos_Impuestos_Real+ Ingresos_Contrib_De_Mejoras_Real+ Ingresos_Derechos_Real + Ingresos_Productos_Real + Ingresos_Aprovechamientos_Real + Ingresos_Quotas_SS_Real )/ Ingresos_Total_Real)*10

* Generation of variables reflecting the share of non-earmarked federal transfers a local government receives (per capita as well as as share of total revenues)
gen Participaciones_Share= Ingresos_Particip_Real/ Ingresos_Total_Real
gen Participaciones_Cap = Ingresos_Particip_Real/ Population

* Generation of variables reflecting the share of earmarked federal transfers a local government receives (per capita as well as as share of total revenues)
gen Aportaciones_Share= Ingresos_Aport_Fed_Est_Real / Ingresos_Total_Real
gen Aportaciones_Cap= Ingresos_Aport_Fed_Est_Real/Population

* Generation of variables reflecting the share of federal transfers (earmarked and non-earmarked) a local government receives (per capita as well as as share of total revenues)
gen Ingv_Transfers_Share = ( Ingresos_Particip_Real+ Ingresos_Aport_Fed_Est_Real)/ Ingresos_Total_Real
gen Ingv_Transfers_Cap = (Ingresos_Particip_Real+ Ingresos_Aport_Fed_Est_Real)/Population

*Generation of a varaiable indicating the share of debt in total local revenue
gen Debt_Share = Ingresos_Financiamientos_Real/ Ingresos_Total_Real

*Generation of a variable indicating the share of local spending per capita (in thousands)
gen Spending_Cap_Real = (Egresos_Total_Real/1000)/ Population

* Separation of data contained in variables of interest into two variables: one indicating the variation of revenue/ spending between municipal governments and one indicating variation within local governments over time, for use as part of the hybrid model specification (munic. govts. as level 2, years as level 1)
egen mn_Own_Revenue_Share= mean(Own_Revenue_Share), by(code)
gen dev_Own_Revenue_Share= Own_Revenue_Share- mn_Own_Revenue_Share
egen mn_Spending_Cap_Real_Munic = mean(Spending_Cap_Real), by(code)
gen dev_Spending_Cap_Real_Munic = Spending_Cap_Real - mn_Spending_Cap_Real_Munic

* For better interpretation of results, decimal of the HDI and the gini coefficient is moved one numeral to the right
replace gini = gini*10
replace HDI_Services = HDI_Services*10

* Generating variables indicating general homicides, organized crime homicides and organized crime events per per capita (per 1000 inhabitants)
gen Homicides_All_Cap = Homicides_All/ Population*1000
gen CIDE_OC_deaths_Cap= CIDE_OC_deaths/ Population*1000

* Generating a dummy variable for homicides or OCG events having occurred in a municipality (1) or not (0)
gen participation_hom = 0 if Homicides_All_Cap ==0 
replace Homicides_All_Cap = -1 if Homicides_All_Cap ==.
replace participation_hom = 1 if Homicides_All_Cap >0 
replace Homicides_All_Cap = . if Homicides_All_Cap ==-1

gen participation_OC_CIDE_deaths = 0 if CIDE_OC_deaths_Cap ==0
replace CIDE_OC_deaths_Cap = -1 if CIDE_OC_deaths_Cap ==.
replace participation_OC_CIDE_deaths = 1 if CIDE_OC_deaths_Cap >0
replace CIDE_OC_deaths_Cap = . if CIDE_OC_deaths_Cap ==-1

*Generating the log of (positive) homicides rates 
gen Homicides_All_Cap_Pos_Log = Homicides_All_Cap if participation_hom==1
replace Homicides_All_Cap_Pos_Log =log(Homicides_All_Cap_Pos)
gen Homicides_All_Cap_Pos = Homicides_All_Cap if participation_hom==1

gen CIDE_OC_deaths_Cap_Log = CIDE_OC_deaths_Cap  if participation_OC_CIDE_deaths ==1
replace CIDE_OC_deaths_Cap_Log =log(CIDE_OC_deaths_Cap_Log)
gen CIDE_OC_deaths_Cap_Pos = CIDE_OC_deaths_Cap if participation_OC_CIDE_deaths ==1

* Generating a variable reflecting the municipal security spending per capita (per 100 pesos) in 2010 and 2014. Since most complete data are available on the share of security spending of total spending, the data needs to be converted to absolute spending first. Note that since Population data are only available for 2010 and not for 2014 the per capita variable only containts values for 2010 
gen Sec_Spending_real = (((Sec_Spending_Share/100)*Egresos_Total_Real))
replace Sec_Spending_real=. if Sec_Spending_real==0
gen Sec_Spending_Cap_2010 = ((Sec_Spending_real)/100)/ Population

* Using the security spending data for 2014 as proxy for the 2015 security spending data (as the respective data is gathered only on a biannual basis, data for 2015 do not exist). 
gen Sec_Spending_real_Lag = Sec_Spending_real[_n-1]
gen Sec_Spending_Cap_Lag_2015 = ((Sec_Spending_real_Lag)/100)/ Population

*Merging the security spending variables for 2010 and 2015 into one
gen Sec_Spending_Cap_Lag_1015 = Sec_Spending_Cap_2010
replace Sec_Spending_Cap_Lag_1015= Sec_Spending_Cap_Lag_2015 if year==2015

* Generating a variable reflectin the number of police per head (per 1000 inhabitants)
gen Loc_Police_Per_Head = Police_Municipal/ Population*1000

*Applying linear interpolation for the year 2015 for which data on local policy do not exist
by code: ipolate Police_Municipal year, gen(Police_Municipal_Ipol)
gen Loc_Police_Per_Head_Ipol = Police_Municipal_Ipol/ Population*1000

*Generating variables on the share of female headed households of total households, share of males and female population
gen Share_Head_of_HH_Female = Number_Head_of_HH_Female/ Households_Total*100
replace Population_Males = Population_Males/ Population*100
replace Population_Females = Population_Females / Population*100

*Generating the variable reflecting the share of indigenous population of total population
gen Indigenous_Pop_Share = Indigenous_Pop_Numb/Population*100

*Generating a variable indicating if a local government is located in a state bordering the United States
gen Dummy_Border=1 if Estado_ID ==26 | Estado_ID==2| Estado_ID==8| Estado_ID==5| Estado_ID==19| Estado_ID==28
replace Dummy_Border = 0 if Dummy_Border ==.

*Generating a variable indicating if a local government is located in a state bordering the United States or at the pacific coast
gen Dummy_Border_PCoast =1 if Estado_ID ==26 | Estado_ID==2| Estado_ID==8| Estado_ID==5| Estado_ID==19| Estado_ID==28 | Estado_ID==25 | Estado_ID==16 | Estado_ID==12 | Estado_ID==18
replace Dummy_Border_PCoast = 0 if Dummy_Border_PCoast  ==.

* Generating a variable indicating the population density (population in 100 inhabitants)
gen Population_Density = (Population/100)/Area_Size

*Generating a categorical population variable classifying municipalities according to the quartile they are located in 
xtile Munic_Size_Categ = Population, nq(4)

*Calculating the Laakso-Taagepera index: Using tsfill because the variables related to the votes for each party (pri- total) containt only for some municipalities observations years that go back until 1985.
xtset code year
tsfill
* The variables containing the number of votes (pri-total) are string, destring needs to be applied
destring pri, replace force
destring pan , replace force
destring prd , replace force
destring parm , replace force
destring pdm , replace force
destring pps , replace force
destring prt , replace force
destring prv , replace force
destring pt , replace force
destring pvem , replace force
destring pc, replace force
destring otros , replace force
destring noreg , replace force
destring anulados , replace force
destring pps1, replace force
destring total , replace force

*A macro is applied to calculate the index. Since it is assumed that the number of effective parties is important also for non-election years, and since in most municipalities mayors are elected for a three year term (with important exceptions: Hidalgo from 2011 on, Veracruz only in 2000, Coahuila from 2005 on) the number of votes are extended to non-election years. 

*Start dealing with those states where local elections follow a four-year-term logic
gen Four_years = 1 if Estado_ID==5 & year >=2005
replace Four_years=2 if Estado_ID==13 & year>=2011
replace Four_years =3 if Estado_ID==30 & year >=2000 & year<=2003
replace Four_years=0 if Four_years==.

* Fill the four-year terms with the number of votes from election years by shifting a copied variable 
foreach var of varlist pri-total {
by code (year), sort: gen `var'_copy = `var'
by code (year), sort: replace `var' = `var'_copy[_n-1] if `var'==. & Four_years>0
}
drop pri_copy - total_copy
foreach var of varlist pri-total {
by code (year), sort: gen `var'_copy = `var'
by code (year), sort: replace `var' = `var'_copy[_n-1] if `var'==. & Four_years>0
}
drop pri_copy - total_copy
foreach var of varlist pri-total {
by code (year), sort: gen `var'_copy = `var'
by code (year), sort: replace `var' = `var'_copy[_n-1] if `var'==. & Four_years>0
}
drop pri_copy - total_copy

* Applying a downward cascade command for those municipalities for which the three-year-term rule applies
foreach var of varlist pri-total {
by code (year), sort: replace `var'=-1 if missing(`var')  & Four_years==0
by code (year), sort: replace `var'=-2 if `var'==-1 &  `var'[_n-3]>-1 & Four_years==0
by code (year) , sort: replace `var'=`var'[_n-1] if `var'==-1 & Four_years==0
by code (year), sort: replace `var'=. if `var' ==-2 & Four_years==0
rename `var' `var'_Votes
}
drop Four_years
sort code year
*Due to gaps in the data on local election results in particular from 2010 on, these had to be complemented by a separate data gathering process. These will now be added
merge code year using Elect_Comp_Erg, update replace
drop _merge
sort code year
merge code year using Elect_Comp_Erg_2015, update replace
drop _merge

* The order of variables in the dataset needs to be adjusted
order noreg, last
order anulados, last
order total, last
order otros_Votes, before(noreg_Votes)
order panal_Votes, before(otros_Votes)
order pds0_Votes - total_Votes, after(ups_Votes)
drop total_Votes
egen total_Votes =rowtotal(pri_Votes- panal_Votes)

* An indicator for whether a specific party participated in a local election is generated
foreach var of varlist pri_Votes- panal_Votes {
gen `var'_part = 1 if `var'>=0 
replace `var'_part = 0 if `var'==.
}

*A sum variable of the participating parties is being calculated 
egen Number_Parties=rowtotal(pri_Votes_part- panal_Votes_part)

* Applying the formula proposed by Laakso & Taagepera (1979) * 
foreach var of varlist pri_Votes- panal_Votes {
gen `var'_ShSQ = `var'/total_Votes*`var'/total_Votes
}
egen Total_ShSQ=rowtotal(pri_Votes_ShSQ- panal_Votes_ShSQ)
gen Laakso_Tag_Ind = 1/Total_ShSQ

* Those municipalities of the State of Oaxaca governed by traditional rules are marked as having one effective party
replace Laakso_Tag_Ind=1 if Estado_ID ==20 & Laakso_Tag_Ind==.

*For 1995 now data on local elections in Oaxaca are available, which is why these municipalities display a missing value for 1997 and before
replace Laakso_Tag_Ind=. if Estado_ID ==20 & year<1998

* Deleting support variables
drop pri_Votes_part- panal_Votes_part
drop pri_Votes- anulados_Votes
drop pri_Votes_ShSQ- panal_Votes_ShSQ
drop Number_Parties Total_ShSQ total_Votes

* Include variables indicating whether a municipality declared a desaster or not (groups: hurricane, extreme temperatures, frosting , earthquakes and volcancic activity, strong rains and inundations) 

* Include variables indicating whether a municipality declared a desaster or not (groups: hurricane, extreme temperatures, frosting , earthquakes and volcancic activity, strong rains and inundations)
sort code year
merge code year using Dec_Des_collapsed
foreach var of varlist Group_cicl_des_em_dummy- Group_lluv_inund_des_em_dummy{
replace `var'=0 if `var'==. 
}
foreach var of varlist Group_cicl_des_em_dummy- Group_lluv_inund_des_em_dummy{
replace `var'=. if `var'==0 & year <2000
}

sort code year


* convert Population to 10 000s
replace Population = Population/10000


*Due to the merging procedure, some specifications of the code variable indicate if a data relates to a territory other than a municipality. These identifiers need to be deleted
drop if code == 1996
drop if code == 1997
drop if code == 1998
drop if code == 2996
drop if code == 1997
drop if code== 2997
drop if code == 2998
drop if code == 3996
drop if code == 3997
drop if code == 3998
drop if code == 4996
drop if code == 4997
drop if code == 4998
drop if code == 5996
drop if code == 5997
drop if code == 5998
drop if code == 6996
drop if code == 6997
drop if code == 6998
drop if code == 7996
drop if code == 7998
drop if code == 7997
drop if code ==8995
drop if code == 8996
drop if code == 8997
drop if code == 8998
drop if code == 9996
drop if code == 9997
drop if code == 9998
drop if code == 10996
drop if code == 10997
drop if code == 10998
drop if code == 11996
drop if code == 11997
drop if code == 11998
drop if code == 12996
drop if code == 12997
drop if code == 12998
drop if code == 13996
drop if code == 13997
drop if code == 13998
drop if code == 14996
drop if code == 14997
drop if code == 14998
drop if code == 14996
drop if code ==15996
drop if code ==15997
drop if code == 15998
drop if code == 16996
drop if code == 16997
drop if code == 16998
drop if code == 17996
drop if code == 17997
drop if code == 17998
drop if code == 18996
drop if code == 18997
drop if code == 18998
drop if code == 19996
drop if code == 19997
drop if code == 19998
drop if code == 20996
drop if code == 20997
drop if code == 20998
drop if code == 20996
drop if code == 21996
drop if code == 21997
drop if code == 21998
drop if code == 22996
drop if code == 22997
drop if code == 22998
drop if code == 23996
drop if code == 23997
drop if code == 23998
drop if code == 24996
drop if code == 24997
drop if code == 24998
drop if code == 25996
drop if code == 25997
drop if code == 25998
drop if code == 26996
drop if code == 26997
drop if code == 26998
drop if code == 27996
drop if code == 27997
drop if code == 27998
drop if code == 28996
drop if code == 28997
drop if code == 28998
drop if code == 29996
drop if code == 29997
drop if code == 29998
drop if code == 30996
drop if code == 30997
drop if code == 30998
drop if code == 31996
drop if code == 31997
drop if code == 31998
drop if code == 31996
drop if code == 32996
drop if code == 32997
drop if code == 32998
drop if code ==19
sort code year
drop if code ==.
